/**
 * Copyright 2016-2017 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.inmorn.extspring.jdbc.oracle;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.RowMapper;

import com.inmorn.extspring.exception.DaoException;
import com.inmorn.extspring.metadata.IPage;
import com.inmorn.extspring.metadata.Page;
import com.inmorn.extspring.metadata.PageFooterColumn;
import com.inmorn.extspring.metadata.QueryPage;


public class BaseJdbcDao extends com.inmorn.extspring.jdbc.BaseJdbcDao{

	@SuppressWarnings("rawtypes")
	public IPage<?> queryByPage(String sql, List<Object> args, QueryPage queryPage, RowMapper rowMapper) throws DaoException {
		return queryByPage(sql, args, queryPage.getPageSize(), queryPage.getPageIndex(), rowMapper);
	}
	
	@SuppressWarnings("rawtypes")
	public IPage<?> queryByPage(String sql, List<Object> args, int pageSize,
			int pageIndex, RowMapper rowMapper) throws DaoException {
		
		QueryPage queryPage = new QueryPage(pageSize, pageIndex);
		queryPage.setSqlString(sql);
		if (args != null) {
			for (Object arg : args) {
				queryPage.addQueryCondition(null, arg);
			}
		}
		queryPage.setRowMapper(rowMapper);
		
		return queryByPage(queryPage);
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public IPage<?> queryByPage(QueryPage queryPage)
			throws DaoException {
		
		String sql = queryPage.getSqlString();
		List args = queryPage.getAllNotNullArg();
		int pageSize = queryPage.getPageSize();
		int pageIndex = queryPage.getPageIndex();
		RowMapper rowMapper = queryPage.getRowMapper();
		boolean calCount = queryPage.isCalCount();
		Map<String, String> sortMap = queryPage.getSortMap();
		
		List<PageFooterColumn> footers = new ArrayList<PageFooterColumn>();
		List<Object> params = new ArrayList<Object>();
		
		if (args != null && args.size() > 0) {
			for (Object arg : args) {
				if ( (arg instanceof PageFooterColumn) )
					footers.add((PageFooterColumn) arg);
				else
					params.add(arg);
			}
		}
		
		// 汇总
		Map<String, Object> aggregations = null;
		Integer total = pageSize;
		if (calCount) {
			aggregations = aggregate(sql, params, footers);
			total = Integer.valueOf(aggregations.get(COUNT).toString());
		}

		// 如果有排序条件，在sqlString后添加order by
		StringBuffer sbSql = new StringBuffer(sql);
		if (sortMap != null && !sortMap.isEmpty()) {
			sbSql.append(ORDER_BY);
			int i = 0;
			for (Iterator it = sortMap.keySet().iterator(); it
					.hasNext();) {
				Object o = it.next();
				String fieldName = o.toString();
				String orderType = sortMap.get(o.toString())
						.toString();

				if (i > 0) {
					sbSql.append(COMMA);
				}

				if (ASC.equalsIgnoreCase(orderType)) {
					sbSql.append(fieldName)
							.append(SPACE).append(ASC);
				} else {
					sbSql.append(fieldName)
							.append(SPACE).append(DESC);
				}
				i++;
			}
			
			sbSql.append(COMMA).append("sys_guid()");
		}

		// 翻页
		int newPageIndex = pageIndex;
		if (calCount) {
			if (pageIndex < 0) {
				newPageIndex = 0;
			}
			else if (total <= pageSize * (pageIndex)) {
				if (total > 0)
					newPageIndex = (total + pageSize - 1) / pageSize - 1;
			}
		}
		
		int firstResult = pageSize * (newPageIndex) + 1;
		params.add(firstResult); // FirstResult
		params.add(pageSize + firstResult); // MaxResults

		sql = "select * from ( select TMP_RESULT.*, ROWNUM RN from (" + sql
				+ ") TMP_RESULT ) where RN >= ? and RN<= ? ";

		Page page = new Page(
				getJdbcTemplate().query(sql, params.toArray(), rowMapper), total,
				pageSize, newPageIndex);
		page.setUserdata(aggregations);
		
		return page;
	}
	
	public int count(String sql, List<Object> args) throws DaoException {
		String countSql = "select count(*) from (" + sql + ")";
		
		if(logger.isDebugEnabled())
			logger.debug("countSql:" + countSql);
		
		if (null != args)
			return getJdbcTemplate().queryForObject(countSql, args.toArray(),Integer.class);
		else
			return getJdbcTemplate().queryForObject(countSql,Integer.class);
	}
	
	public Map<String, Object> aggregate(String sql, List<Object> args, List<PageFooterColumn> footers) {
		StringBuilder aggSql = new StringBuilder();
		aggSql.append("select count(*) as ");
		aggSql.append(COUNT).append(SPACE);
		if (footers != null) {
			for (PageFooterColumn column : footers) {
				aggSql.append(",").append(column.getAggExpression()).append(" as ").append(column.getName());
			}
		}
		aggSql.append(" from (").append(sql).append(")");
		
		if(logger.isDebugEnabled())
			logger.debug("aggSql:" + aggSql.toString());
		Map<String, Object> map = this.getJdbcTemplate().queryForMap(aggSql.toString(), args.toArray());
		if (footers != null) {
			for (PageFooterColumn column : footers) {
				String key = column.getName().toUpperCase();
				Object value = map.get(key);
				map.remove(key);
				map.put(column.getName(), value);
			}
		}
		
		return map;
	}
	
	/**
	 * 函数查询总页数 ，设置超时查询时调用此方法
	 * timeOut set
	 * queryPage
	 */
	public Map<String, Object> aggregate(String sql, List<Object> args, List<PageFooterColumn> footers,final int timeOut) {
		StringBuilder aggSql = new StringBuilder();
		aggSql.append("select count(*) as ");
		aggSql.append(COUNT).append(SPACE);
		if (footers != null) {
			for (PageFooterColumn column : footers) {
				aggSql.append(",").append(column.getAggExpression()).append(" as ").append(column.getName());
			}
		}
		aggSql.append(" from (").append(sql).append(")");
		
		if(logger.isDebugEnabled())
			logger.debug("aggSql:" + aggSql.toString());
		if(timeOut>0){
			this.getJdbcTemplate().setQueryTimeout(timeOut);
		}
		Map<String, Object> map = this.getJdbcTemplate().queryForMap(aggSql.toString(), args.toArray());
		if (footers != null) {
			for (PageFooterColumn column : footers) {
				String key = column.getName().toUpperCase();
				Object value = map.get(key);
				map.remove(key);
				map.put(column.getName(), value);
			}
		}
		
		return map;
	}
	

	/**
	 * 分页查询
	 * @param sql		分页查询sql
	 * @param countSql 	count记录数SQL
	 * @param args		查询条件
	 * @param queryPage	页码，每页显示行数等查询条件
	 * @param rowMapper	对实体类进行赋值
	 * @return
	 * @throws DaoException
	 */
	@SuppressWarnings({ "unchecked", "rawtypes", "unused" })
	public Page queryByMotPage(String sql, String countSql, List<Object> args, QueryPage queryPage, RowMapper rowMapper)throws DaoException {
		int record = this.getJdbcTemplate().queryForObject(countSql, args.toArray(),Integer.class);
		int rows = queryPage.getPageSize();	// 每页显示行数
		int page = queryPage.getPageIndex();// 第几页
		// rn分页查询的起始行数，rownum分页查询终止行数 
		int rn = page * rows + 1;
		int rownum = (page + 1) * rows;
		if(args == null)
			return new Page(this.getJdbcTemplate().query(sql, rowMapper), record, rows, page);
		
		args.add(rownum);
		args.add(rn);
		return new Page(this.getJdbcTemplate().query(sql, args.toArray(), rowMapper), record, rows, page);
		
	}
}
